﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
    public class BannerUtil:IDAL<Banner>
    {
        public int Add(Banner info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Banner info)
        {
            throw new NotImplementedException();
        }

        public Banner GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Banner GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Banner info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Banner> SearchCollection(StringBuilder condtion)
        {
            ICollection<Banner> infos = new List<Banner>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"WITH    CTE1
                      AS ( SELECT   T.REF_PARAM AS KA_CD ,
                                    CU.CUSTHIER_PAR ,
                                    CU.CUSTHIER_CD AS CUSTOMER_CD ,
                                    CU.CUSTHIER_DESC AS CUSTOMER_DESC ,
                                    T.REF_VALUE AS KA_DESC
                           FROM     SET_REF T
                                    INNER JOIN DBO.MST_CUSTHIER CU ON CU.REF_VALUE = T.REF_PARAM
                                                                      AND CU.CUSTHIER_LEVEL = '3'
                                                                      AND T.REF_TYPE = 'C_KEYACC'
                                                                      AND T.SYNCOPERATION <> 'D'
                                                                      AND CU.SYNCOPERATION <> 'D'
                         ),
                    CTE2
                      AS ( SELECT   CTE1.* ,
                                    BA.CUSTHIER_CD AS BA_CD ,
                                    BA.CUSTHIER_DESC AS BA_DESC ,
                                    BA.CUSTHIER_PAR AS BA
                           FROM     CTE1
                                    INNER JOIN DBO.MST_CUSTHIER BA ON BA.CUSTHIER_CD = CTE1.CUSTHIER_PAR
                                                                      AND BA.CUSTHIER_LEVEL = '2'
                                                                      AND BA.SYNCOPERATION <> 'D'
                         ),
                    CTE3
                      AS ( SELECT   CTE2.* ,
                                    CH.CUSTHIER_CD AS CH_CD ,
                                    CH.CUSTHIER_DESC AS CH_DESC
                           FROM     CTE2
                                    INNER JOIN DBO.MST_CUSTHIER CH ON CH.CUSTHIER_CD = CTE2.BA
                                                                      AND CH.CUSTHIER_LEVEL = '1'
                                                                      AND CH.SYNCOPERATION <> 'D'
                         )
                SELECT  KA_CD ,
                        KA_DESC ,
                        CUSTOMER_CD ,
                        CUSTOMER_DESC ,
                        BA_CD ,
                        BA_DESC ,
                        CH_CD ,
                        CH_DESC
                FROM    CTE3");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

        public Banner SetInfo(System.Data.DataRow row)
        {
            Banner banner = new Banner();
            banner.KA_CD = SqlHelper.GetString(row, "KA_CD");
            banner.KA_DESC = SqlHelper.GetString(row, "KA_DESC");
            banner.CUSTOMER_CD = SqlHelper.GetString(row, "CUSTOMER_CD");
            banner.CUSTOMER_DESC = SqlHelper.GetString(row, "CUSTOMER_DESC");
            banner.BA_CD = SqlHelper.GetString(row, "BA_CD");
            banner.BA_DESC = SqlHelper.GetString(row, "BA_DESC");
            banner.CH_CD = SqlHelper.GetString(row, "CH_CD");
            banner.CH_DESC = SqlHelper.GetString(row, "CH_DESC");

            return banner;
        }
    }
}
